#include "data.h"


// constructor
CashierData::CashierData(PGconn *dbconn)
{
    conn = dbconn;
    
    // get some default settings from registry
    config = new wxConfig(wxT("slpos"));
    DefaultCustomer = config->Read(wxT("Customer"));
    CashAccount     = config->Read(wxT("CashAccount"));
    CardAccount     = config->Read(wxT("CardAccount"));
    ARaccount       = config->Read(wxT("ARaccount"));

    MarginTop       = config->Read(wxT("MarginTop"), wxT("0"));
    MarginLeft      = config->Read(wxT("MarginLeft"), wxT("0"));
    PrintFooter     = config->Read(wxT("PrintFooter"));
    
    CompanyName     = config->Read(wxT("CompanyName"));
    CompanyAddress  = config->Read(wxT("CompanyAddress"));
    CompanyPhone    = config->Read(wxT("CompanyPhone"));

    delete config;
};


// deconstructor
CashierData::~CashierData()
{
};


bool CashierData::SaveOrder(wxDateTime TransDate, bool IsCashPayment, listctrlItem* ItemList)
{
    PGresult *res;
    double GrandTotal = ItemList->GetGrandTotal();
    wxString strqry;
    wxString newID;
    
    // start a transaction block
    res = PQexec(conn, "BEGIN");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to start a transaction: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    
    // get the ID sequence latest value
    strqry = wxT("SELECT nextval('id'::regclass)");
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        LastError.Printf(_("Unable to get a sequence number from database: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    } else {
        newID = PQgetvalue(res, 0, 0);
        PQclear(res);
    };
    
    // save to table "ar"
    strqry.Empty();
    strqry = wxT("INSERT INTO ar (id, invnumber, transdate, customer_id, taxincluded, amount, ");
    strqry += wxT("netamount, paid, datepaid, duedate, invoice, curr, employee_id, till, cashdiscount, ");
    strqry += wxT("discountterms, warehouse_id, exchangerate, bank_id, paymentmethod_id) VALUES (");
    strqry += wxT("'") + newID + wxT("', ");
    strqry += wxT("'CASH', ");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
    strqry += wxT("'") + DefaultCustomer + wxT("', ");
    strqry += wxT("'FALSE', ");
    strqry << GrandTotal;
    strqry += wxT(", ");
    strqry << GrandTotal;
    strqry += wxT(", ");
    strqry << GrandTotal;
    strqry += wxT(", ");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
    strqry += wxT("'TRUE', ");
    strqry += wxT("'IDR', ");
    strqry += wxT("10060, ");
    strqry += wxT("'1', ");
    strqry += wxT("0, ");
    strqry += wxT("0, ");
    strqry += wxT("0, ");
    strqry += wxT("1, ");
    strqry << CashAccount;
    strqry += wxT(", ");
    strqry += wxT("0)");
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to save to database: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    };
    
    PQclear(res);
    
    // for each item in the list
    
    // get from the PARTS table the oid, description, sellprice, income_accno_id, unit, assembly
    long oid;
    wxString description, sellprice, quantity, discount, linetotal;
    wxString income_acc_id, unit, assembly;
    wxString newInvID;
    
    //get the number of items from the current list
    int totalitems = ItemList->GetItemCount();
    
    for (long i = 0; i <= totalitems-1; i++) {
        wxListItem desc, qty, price, disc, lnttl;
        
        // get oid
        oid = ItemList->GetItemData(i);
        // get description
        desc.m_mask = wxLIST_MASK_TEXT;
        desc.m_itemId = i;
        desc.m_col = 1;
        ItemList->GetItem(desc);
        description = desc.GetText();
        // get item quantity
        qty.m_mask = wxLIST_MASK_TEXT;
        qty.m_itemId = i;
        qty.m_col = 2;
        ItemList->GetItem(qty);
        quantity = qty.GetText();
        // get item price
        price.m_mask = wxLIST_MASK_TEXT;
        price.m_itemId = i;
        price.m_col = 3;
        ItemList->GetItem(price);
        sellprice = price.GetText();
        // get item discount
        disc.m_mask = wxLIST_MASK_TEXT;
        disc.m_itemId = i;
        disc.m_col = 4;
        ItemList->GetItem(disc);
        discount = disc.GetText();
            // convert to the real discount value
            double temp;
            discount.ToDouble(&temp); temp = temp / 100;
            discount.Empty();
            discount << temp;
            
        // get item price
        lnttl.m_mask = wxLIST_MASK_TEXT;
        lnttl.m_itemId = i;
        lnttl.m_col = 5;
        ItemList->GetItem(lnttl);
        linetotal = lnttl.GetText();
        
        // get the remaining value from PARTS table
        strqry.Empty();
        strqry = wxT("SELECT income_accno_id, unit, assembly FROM parts WHERE id = ");
        strqry << oid;
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            LastError.Printf(_("Unable to get data from parts table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        };
        if ( PQntuples(res) == 1 )
        {
            income_acc_id = wxT(PQgetvalue(res, 0, 0));
            unit = wxT(PQgetvalue(res, 0, 1));
            assembly = wxT(PQgetvalue(res, 0, 2));
        };
        
        PQclear(res);
        
        // UPDATING invoice TABLE
        /// get the INVOICEID sequence latest value
        strqry.Empty();
        strqry = wxT("SELECT nextval('invoiceid'::regclass)");
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            LastError.Printf(_("Unable to get a sequence number from database: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        } else {
            newInvID = PQgetvalue(res, 0, 0);
            PQclear(res);
        };
        
        /// save to INVOICE table 
        strqry.Empty();
        strqry = wxT("INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated, ");
        strqry += wxT("sellprice, fxsellprice, discount, assemblyitem, unit, lineitemdetail) VALUES (");
        strqry += newInvID + wxT(", ");
        strqry += newID + wxT(", ");
        strqry << oid;
        strqry += wxT(", ");
        strqry += wxT("'") + description + wxT("', ");
        strqry += wxT("'") + quantity + wxT("', ");
        strqry += wxT("0, ");
        strqry += linetotal + wxT(", ");
        strqry += sellprice + wxT(", ");
        strqry += discount + wxT(", ");
        strqry += wxT("'") + assembly + wxT("', ");
        strqry += wxT("'") + unit + wxT("', ");
        strqry += wxT("FALSE)");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to invoice table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        /// save to ACC_TRANS table
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += income_acc_id + wxT(", ");
        strqry += linetotal + wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += newInvID + wxT(")");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        /// update the ONHAND field of the PARTS table
        /// substract the quantity with the existing quantity
        strqry.Empty();
        strqry = wxT("UPDATE parts SET onhand = onhand - ");
        strqry += quantity;
        strqry += wxT(" WHERE id = ");
        strqry << oid;
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to update PARTS table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
    };
    
    /// save to ACC_TRANS table
    strqry.Empty();
    strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
    strqry += newID + wxT(", ");
    strqry += ARaccount + wxT(", ");
    strqry << GrandTotal * -1;
    strqry += wxT(", ");
    strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
    
    res = PQexec(conn, strqry.char_str());
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    
    /// save the payment to the ACC_TRANS table
    if(IsCashPayment) {
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
        strqry += newID + wxT(", ");
        strqry += ARaccount + wxT(", ");
        strqry << GrandTotal;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += CashAccount + wxT(", ");
        strqry << GrandTotal * -1;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += wxT("1)");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
    } else {
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate) VALUES (");
        strqry += newID + wxT(", ");
        strqry += ARaccount + wxT(", ");
        strqry << GrandTotal;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("')");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
        
        strqry.Empty();
        strqry = wxT("INSERT INTO acc_trans (trans_id, chart_id, amount, transdate, id) VALUES (");
        strqry += newID + wxT(", ");
        strqry += CardAccount + wxT(", ");
        strqry << GrandTotal * -1;
        strqry += wxT(", ");
        strqry += wxT("'") + TransDate.FormatISODate() + wxT("',");
        strqry += wxT("1)");
        
        res = PQexec(conn, strqry.char_str());
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
            LastError.Printf(_("Unable to save to acc_trans table: %s"), PQerrorMessage(conn));
            PQclear(res);
            return false;
        }
        
        PQclear(res);
    };
    
    // end the transaction
    res = PQexec(conn, "END");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        LastError.Printf(_("Unable to end a transaction: %s"), PQerrorMessage(conn));
        PQclear(res);
        return false;
    }
    
    PQclear(res);
    return true;
};


wxString CashierData::CreatePrintReceipt(wxDateTime TransDate, listctrlItem* ItemList, long Payment, bool IsCash)
{
    wxString htmlPrintString;
    
    htmlPrintString = wxT("<html><body><code>");
    
    // print the header
    htmlPrintString += CompanyName + wxT("<br>");
    htmlPrintString += CompanyAddress + wxT("<br>");
    htmlPrintString += CompanyPhone + wxT("<br><br>");
    htmlPrintString += _("Date: ") + TransDate.FormatISODate() + wxT("<br><br>");
    
    // print the column header
    htmlPrintString += wxT("<table border=0 cellpadding=3 cellspacing=0>");
    htmlPrintString += wxT("<tbody>");    
    htmlPrintString += wxT("<tr><td align=center>");
    htmlPrintString += _("Qty");
    htmlPrintString += wxT("</td><td>");
    htmlPrintString += _("Description");
    htmlPrintString += wxT("</td><td align=right>");
    htmlPrintString += _("Amount");
    htmlPrintString += wxT("</td></tr>");
    
    //get the number of items from the current list
    int totalitems = ItemList->GetItemCount();
    
    for (int i = 0; i <= totalitems-1; i++) {
        wxListItem desc, qty, price;
        
        htmlPrintString += wxT("<tr>");
        
        // get item quantity
        qty.m_mask = wxLIST_MASK_TEXT;
        qty.m_itemId = i;
        qty.m_col = 2;
        ItemList->GetItem(qty);
        htmlPrintString += wxT("<td align=center>") + qty.GetText() + wxT("</td>");
        
        // get description
        desc.m_mask = wxLIST_MASK_TEXT;
        desc.m_itemId = i;
        desc.m_col = 1;
        ItemList->GetItem(desc);
        htmlPrintString += wxT("<td>") + desc.GetText().Left(20) + wxT("</td>");
        
        // get item price
        price.m_mask = wxLIST_MASK_TEXT;
        price.m_itemId = i;
        price.m_col = 3;
        ItemList->GetItem(price);
        htmlPrintString += wxT("<td align=right>") + price.GetText() + wxT("</td>");
        
        htmlPrintString += wxT("</tr>");
    };
    
    // print a separator
    htmlPrintString += wxT("<tr>");
    htmlPrintString += wxT("<td>-----</td>");
    htmlPrintString += wxT("<td>--------------------</td>");
    htmlPrintString += wxT("<td align=right>--------</td>");
    htmlPrintString += wxT("</tr>");
    
    // print the total amount
    long GrandTotal = ItemList->GetGrandTotal();
    htmlPrintString += wxT("<tr>");
    htmlPrintString += wxT("<td></td>");
    htmlPrintString += wxT("<td align=right>Subtotal:</td>");
    htmlPrintString += wxT("<td align=right>");
    htmlPrintString << GrandTotal;
    htmlPrintString += wxT("</td>");
    htmlPrintString += wxT("</tr>");
    
    if (IsCash) {
        // print the payment amount
        htmlPrintString += wxT("<tr>");
        htmlPrintString += wxT("<td></td>");
        htmlPrintString += wxT("<td align=right>Paid:</td>");
        htmlPrintString += wxT("<td align=right>");
        htmlPrintString << Payment;
        htmlPrintString += wxT("</td>");
        htmlPrintString += wxT("</tr>");
        
        // print the change amount
        long Change = Payment-GrandTotal;
        htmlPrintString += wxT("<tr>");
        htmlPrintString += wxT("<td></td>");
        htmlPrintString += wxT("<td align=right>Change:</td>");
        htmlPrintString += wxT("<td align=right>");
        htmlPrintString << Change;
        htmlPrintString += wxT("</td>");
        htmlPrintString += wxT("</tr>");
    
    } else {
        // print the payment amount
        htmlPrintString += wxT("<tr>");
        htmlPrintString += wxT("<td></td>");
        htmlPrintString += wxT("<td align=right>Card:</td>");
        htmlPrintString += wxT("<td align=right>");
        htmlPrintString << Payment;
        htmlPrintString += wxT("</td>");
        htmlPrintString += wxT("</tr>");
    };
    
    htmlPrintString += wxT("</tbody>");
    htmlPrintString += wxT("</table>");
    htmlPrintString += wxT("<p>") + PrintFooter + wxT("</p>");
    htmlPrintString += wxT("</code></body></html>");

    // clear the result to avoid memory leak
    return htmlPrintString;
};

